<?php
echo "==========================================\n";
echo "  客户表业务字段优化执行\n";
echo "==========================================\n\n";

try {
    $pdo = new PDO('mysql:host=localhost;dbname=oa5;charset=utf8mb4', 'root', '123456', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    echo "✓ 数据库连接成功\n\n";

    // 读取SQL文件
    $sqlFile = __DIR__ . '/update_customer_business_fields.sql';
    $sql = file_get_contents($sqlFile);
    echo "✓ 读取SQL文件成功\n";
    echo "  文件大小: " . number_format(filesize($sqlFile) / 1024, 2) . " KB\n\n";

    // 分割SQL并执行
    $statements = explode(';', $sql);
    $executed = 0;
    $errors = [];

    foreach ($statements as $idx => $stmt) {
        $stmt = trim($stmt);
        if (empty($stmt) || preg_match('/^(--|\/\*)/', $stmt)) continue;

        // 处理DELIMITER
        if (preg_match('/^DELIMITER/', $stmt)) {
            continue;
        }

        // 如果是触发器定义
        if (strpos($stmt, 'CREATE TRIGGER') !== false) {
            try {
                $pdo->exec($stmt);
                $executed++;
                echo "  ✓ 创建触发器成功\n";
            } catch (Exception $e) {
                // 检查是否是已存在错误
                if (strpos($e->getMessage(), 'already exists') !== false ||
                    strpos($e->getMessage(), '已存在') !== false) {
                    echo "  ⊙ 触发器已存在，跳过\n";
                } else {
                    $errors[] = $e->getMessage();
                    echo "  ✗ 触发器创建失败: " . $e->getMessage() . "\n";
                }
            }
        } else {
            try {
                $pdo->exec($stmt);
                $executed++;
                if ($idx % 10 === 0) {
                    echo "  ✓ 执行进度: " . $executed . " 条语句\n";
                }
            } catch (Exception $e) {
                // 忽略"字段已存在"错误
                if (strpos($e->getMessage(), 'Duplicate column name') !== false ||
                    strpos($e->getMessage(), 'already exists') !== false ||
                    strpos($e->getMessage(), '已存在') !== false) {
                    echo "  ⊙ 字段已存在，跳过\n";
                } else {
                    $errors[] = $e->getMessage();
                    echo "  ✗ 错误: " . $e->getMessage() . "\n";
                }
            }
        }
    }

    echo "\n==========================================\n";
    echo "  执行结果\n";
    echo "==========================================\n\n";
    echo "成功执行: " . $executed . " 条语句\n";

    if (empty($errors)) {
        echo "✓ 所有操作执行成功！\n\n";
    } else {
        echo "✗ 出现 " . count($errors) . " 个错误\n\n";
    }

    // 验证字段
    echo "验证字段添加结果:\n";
    $stmt = $pdo->query('
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = "oa5"
        AND table_name = "oa_customer"
        AND column_name IN ("is_member", "contract_num", "case_num")
    ');

    $fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (count($fields) === 3) {
        echo "✓ 所有字段已成功添加: " . implode(', ', $fields) . "\n\n";
    } else {
        echo "✗ 部分字段未添加: " . implode(', ', $fields) . "\n\n";
    }

    // 验证触发器
    echo "验证触发器创建结果:\n";
    $stmt = $pdo->query('
        SELECT trigger_name
        FROM information_schema.triggers
        WHERE trigger_schema = "oa5"
        AND event_object_table IN ("contract", "lawcase")
    ');

    $triggers = $stmt->fetchAll(PDO::FETCH_COLUMN);
    echo "✓ 已创建 " . count($triggers) . " 个触发器:\n";
    foreach ($triggers as $trigger) {
        echo "  - " . $trigger . "\n";
    }
    echo "\n";

    // 统计数据
    echo "数据统计:\n";
    $stmt = $pdo->query('
        SELECT
            COUNT(*) AS total_customers,
            SUM(is_member = 1) AS member_customers,
            SUM(contract_num > 0) AS customers_with_contracts,
            SUM(case_num > 0) AS customers_with_cases
        FROM oa_customer
        WHERE delete_time = 0
    ');

    $stats = $stmt->fetch();
    echo "  总客户数: " . number_format($stats['total_customers']) . "\n";
    echo "  会员客户数: " . number_format($stats['member_customers']) . "\n";
    echo "  有合同客户数: " . number_format($stats['customers_with_contracts']) . "\n";
    echo "  有案件客户数: " . number_format($stats['customers_with_cases']) . "\n";
    echo "\n";

    echo "==========================================\n";
    echo "  优化完成！\n";
    echo "==========================================\n";

} catch (Exception $e) {
    echo "\n✗ 执行失败: " . $e->getMessage() . "\n";
    exit(1);
}
